﻿using assignment6.Entitty;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Diagnostics;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms.VisualStyles;

namespace assignment6.Service
{
    internal class SqlService
    {
        readonly static string[] tables = { "School", "Class", "Student" };//对应数据库三个表
        static string connectionString = System.Configuration.ConfigurationManager.AppSettings["connectionString"];//读配置文件

        public static DataSet query(int index, string condition, string condition_value)
        {
            string table = index < 0 || index > 2 ? "Log" : tables[index];//获取表名
            //如果条件为空则查询全表
            string sql = $"SELECT * FROM {table}";
            if (!String.IsNullOrEmpty(condition))
            {
                sql += $" WHERE {condition} LIKE @condition_value";
            }
            try
            {
                using (SqlConnection conn = new SqlConnection(connectionString))
                {
                    conn.Open();
                    using (SqlCommand command = new SqlCommand(sql, conn))
                    {
                        //为参数赋值
                        command.Parameters.AddWithValue("@condition_value", $"%{condition_value}%");

                        SqlDataAdapter adapter = new SqlDataAdapter(command);
                        DataSet dataSet = new DataSet();
                        adapter.Fill(dataSet);

                        return dataSet; // 返回DataSet对象
                    }
                }
            }
            catch (Exception ex)
            {
                Debug.WriteLine(ex.Message);
                throw ex;
            }
        }

        public static void update(School schools, Class classes, Student students, int index)
        {
            try
            {
                int id = 0;//更新项的ID
                //根据Index选择相应表执行更新
                switch (index)
                {
                    case 0:
                        updateSchool(schools);
                        id = schools.Id;
                        break;
                    case 1:
                        updateClass(classes);
                        id = classes.Id;
                        break;
                    case 2:
                        updateStu(students);
                        id = students.Id;
                        break;
                    default:
                        break;
                }
                //记录更新操作到日志
                logOperate("update", tables[index], id, DateTime.Now);
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public static void add(School school, Class classes, Student student, int index)
        {
            try
            {
                int id = 0;//新增项的ID
                switch (index)
                {
                    case 0:
                        id = addSchool(school);
                        break;
                    case 1:
                        id = addClass(classes);
                        break;
                    case 2:
                        id = addStudent(student);
                        break;
                    default:
                        break;
                }
                //记录新增操作到日志
                logOperate("insert", tables[index], id, DateTime.Now);
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public static void delete(int id, int index)
        {
            try
            {
                string table = tables[index];//获取表名
                using (SqlConnection conn = new SqlConnection(connectionString))
                {
                    conn.Open();
                    string sql = $"DELETE FROM {table} WHERE Id=@id";//SQL语句
                    using (SqlCommand cmd = new SqlCommand(sql, conn))
                    {
                        //为参数赋值
                        cmd.Parameters.AddWithValue("@id", id);
                        //执行语句
                        cmd.ExecuteNonQuery();
                    }
                }
                //记录删除操作到日志
                logOperate("delete", tables[index], id, DateTime.Now);
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        //记录对数据的操作到Log表
        private static void logOperate(string type, string table, int dataId, DateTime time)
        {
            try
            {
                using (SqlConnection conn = new SqlConnection(connectionString))
                {
                    conn.Open();
                    //SQL语句
                    string sql = "INSERT INTO Log(OperateType,OperateDataType,OperateDataId,Time) VALUES (@o_type,@d_type,@d_id,@time) ";
                    using (SqlCommand cmd = new SqlCommand(sql, conn))
                    {
                        //为参数赋值
                        cmd.Parameters.AddWithValue("@o_type", type);
                        cmd.Parameters.AddWithValue("@d_type", table);
                        cmd.Parameters.AddWithValue("@d_id", dataId);
                        cmd.Parameters.AddWithValue("@time", time);
                        //执行
                        cmd.ExecuteNonQuery();
                    }

                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        //更新学生信息
        public static void updateStu(Student student)
        {
            try
            {
                using (SqlConnection conn = new SqlConnection(connectionString))
                {
                    conn.Open();
                    string sql = "UPDATE Student " +
                         "SET Name=@name,Gender=@gender,Age=@age,Class=@class " +
                         "WHERE Id=@id";
                    using (SqlCommand cmd = new SqlCommand(sql, conn))
                    {
                        cmd.Parameters.AddWithValue("@name", student.Name);
                        cmd.Parameters.AddWithValue("@gender", student.Sex);
                        cmd.Parameters.AddWithValue("@age", student.Age);
                        cmd.Parameters.AddWithValue("@class", student.Class.Id);
                        cmd.Parameters.AddWithValue("@id", student.Id);
                        cmd.ExecuteNonQuery();
                    }

                }
            }
            catch (Exception ex)
            {
                Debug.WriteLine(ex.Message);
                throw ex;
            }
        }

        //更新班级信息
        public static void updateClass(Class c)
        {
            try
            {
                using (SqlConnection conn = new SqlConnection(connectionString))
                {
                    conn.Open();
                    string sql = "UPDATE Classes " +
                            "SET Name=@name,School=@school,Teacher=@teacher " +
                            "WHERE Id=@id";
                    using (SqlCommand cmd = new SqlCommand(sql, conn))
                    {
                        cmd.Parameters.AddWithValue("@name", c.Name);
                        cmd.Parameters.AddWithValue("@school", c.School.Id);
                        cmd.Parameters.AddWithValue("@teacher", c.Number);
                        cmd.Parameters.AddWithValue("@id", c.Id);
                        cmd.ExecuteNonQuery();
                    }

                }
            }
            catch (Exception ex)
            {
                Debug.WriteLine(ex.Message);
                throw ex;
            }
        }

        //更新学校信息
        public static void updateSchool(School sc)
        {
            try
            {
                using (SqlConnection conn = new SqlConnection(connectionString))
                {
                    conn.Open();
                    string sql = "UPDATE School " +
                            "SET Name=@name,HeadMaster=@headmaster " +
                            "WHERE Id=@id";
                    using (SqlCommand cmd = new SqlCommand(sql, conn))
                    {
                        cmd.Parameters.AddWithValue("@name", sc.Name);
                        cmd.Parameters.AddWithValue("@headmaster", sc.Number);
                        cmd.Parameters.AddWithValue("@id", sc.Id);
                        cmd.ExecuteNonQuery();
                    }

                }
            }
            catch (Exception ex)
            {
                Debug.WriteLine(ex.Message);
                throw ex;
            }
        }

        //新增学校
        private static int addSchool(School sc)
        {
            try
            {
                using (SqlConnection conn = new SqlConnection(connectionString))
                {
                    conn.Open();
                    string sql = "INSERT INTO School(Name,HeadMaster) VALUES (@name,@headmaster)" +
                        "SELECT SCOPE_IDENTITY()";
                    using (SqlCommand cmd = new SqlCommand(sql, conn))
                    {
                        cmd.Parameters.AddWithValue("@name", sc.Name);
                        cmd.Parameters.AddWithValue("@headmaster", sc.Number);
                        int newId = Convert.ToInt32(cmd.ExecuteScalar());
                        return newId;
                    }

                }
            }
            catch (Exception ex)
            {
                Debug.WriteLine(ex.Message);
                throw ex;
            }
        }

        //新增班级
        private static int addClass(Class c)
        {
            try
            {
                using (SqlConnection conn = new SqlConnection(connectionString))
                {
                    conn.Open();
                    string sql = "INSERT INTO Classes(Name,Teacher,School) VALUES (@name,@teacher,@school) " +
                        "SELECT SCOPE_IDENTITY()";
                    using (SqlCommand cmd = new SqlCommand(sql, conn))
                    {
                        cmd.Parameters.AddWithValue("@name", c.Name);
                        cmd.Parameters.AddWithValue("@school", c.School.Id);
                        cmd.Parameters.AddWithValue("@teacher", c.Number);
                        int newId = Convert.ToInt32(cmd.ExecuteScalar());
                        return newId;
                    }

                }
            }
            catch (Exception ex)
            {
                Debug.WriteLine(ex.Message);
                throw ex;
            }
        }

        //新增学生
        private static int addStudent(Student student)
        {
            try
            {
                using (SqlConnection conn = new SqlConnection(connectionString))
                {
                    conn.Open();
                    string sql = "INSERT INTO Student(Name,Gender,Age,Class) VALUES (@name,@gender,@age,@class) " +
                        "SELECT SCOPE_IDENTITY()";
                    using (SqlCommand cmd = new SqlCommand(sql, conn))
                    {
                        cmd.Parameters.AddWithValue("@name", student.Name);
                        cmd.Parameters.AddWithValue("@gender", student.Sex);
                        cmd.Parameters.AddWithValue("@age", student.Age);
                        cmd.Parameters.AddWithValue("@class", student.Class.Id);
                        int newId = Convert.ToInt32(cmd.ExecuteScalar());
                        return newId;
                    }

                }
            }
            catch (Exception ex)
            {
                Debug.WriteLine(ex.Message);
                throw ex;
            }
        }
    }
}
